MySQL 文字コード

引き続き調査したり記載したりしたいTODO

知りました。じゃあ次にいきましょう。

MySQLで文字コードを設定するパラメータは沢山ある！！

参考

table:

パラメーター 説明

character_set_client クライアントが送ってくるとサーバーが想定している文字コード。

character_set_connection クライアントから受け取った文字をこの文字コードへ変換する。

character_set_database 現在参照しているデータベースの文字コード。変える必要はないしsetで変えてはいけない。

character_set_filesystem ファイル名の文字コード。デフォルトでいい

character_set_results クライアントに結果を返すときの文字コード

character_set_server 新規にデータベースを作るときの文字コード。設定しておくと楽。いろいろ継承されるが上書きできる。

character_set_system テーブル名とかを保存する文字コード。デフォルトでいい

MySQLにおけるデータ挿入やクエリ実行時の文字コード関連の話

正直、公式Doc読んでもいまいち文字コードの変換の流れを理解できなかった。

実機で色々動作検証しながら、なんとなくこんな感じだろうなという仮説をここで記載しとく。

2. 次に、データをデータベースの保存する時は、そのカラムに設定されてる文字コードに変換されることになる。

文字を16進数に変換したら、その様子が見れる。

これは、sjisのやつを意味してるよ。

sjisだと、「あ = 82A0」

送られてきたバイナリデータを別の文字コードとしてデコードしちゃって文字化ける。

「character_set_connection」ってなんやねん...

サーバーは、クライアントによって送信されたステートメントを character_set_client から character_set_connection に変換します。

じゃあ、こいつはなんやねん。。。っていう話よね。

これはデータベース・テーブルのデータと関係ない、ステートメント上の文字リテラルの文字コードの話なんだよ。

いや、ややこしいな本当に。

以下のようなコードがあったとする。

code: sql

SELECT 'あ';

だから、16進数調べたら、sjisのものになってるのがわかる。

code: sample

mysql> show variables like 'character_set_connection';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| character_set_connection | sjis |

+--------------------------+-------+

1 row in set (0.00 sec)

mysql> select hex('あ');

+------------+

| hex('あ') |

+------------+

| 82A0 |

+------------+

1 row in set (0.00 sec)

「なるほどなぁ、送られてくるデータは、一旦この文字コードに変換されて、その上でデータベースに保存されるのかぁ」

これ違う。これはステートメント上で効いてくる文字コードのお話です。保存データの文字コードは、対象テーブルもっと言うとカラムの文字コードに勝手に変換されてます。（character_set_clientから）

code: sql

SELECT 'あ' > v1 from tbl1;

「character_set_database」はマジで勘違いしやすい

マジで勘違いしやすいから気をつけて。

じゃあ、新規データベース作成する時のデフォルト文字コードどこで設定すんねん！？次で話します。

「character_set_server」が新規データベース作る時のデフォルト文字コードです

こいつに設定されてる文字コードこそが、データベース新規作成時のデフォルトに採用されるやつです。

覚えておいてください。

「character_set_system」はMySQL内部で使われる文字コードらしい。触らないのが身のためだとか

こいつはユーザーが触っていい代物ではないとのことです。

「character_set_filesyste」は、まあbinaryでいいらしい

そんな気にせんくていいってよ。

参考

hr.icon

MySQLではデフォルトでlatin1という文字コードになっています。

この文字コードは日本語を扱うことができないため、少し昔であればujisやsjis、いまでは多くの方が3バイトのutf8や、4バイト文字が扱えるutf8mb4に設定して利用されていると思います。

MySQLで設定できる文字コードは、SHOW CHARACTER SET構文で確認することができます。また、SHOW VARIABLES構文から現在設定されている文字コードを確認することができます。

character_set_client

character_set_clientは、クライアント側で発行されたSQLをMySQLに送信する際の文字コードとしてセットします。

なるほど？あまりよくわからんな。

クライアント側からMySQLに送信する際の文字コード。なるほど。

character_set_database

character_set_databaseは、データベースのデフォルトの文字コードを設定します。バージョン5.7からはオンラインで実行することが非推奨となるようになりました。もし、この値がオンラインで設定された場合は、以下のワーニングが出力されます。

データベースってことは、保存されてるデータの文字コードってことか。

なるほど。

character_set_filesystem

ファイルの文字コードを設定できます。LOAD DATA INFILEなどを利用する場合はこちらの文字コードが有効となります。デフォルトはbinaryで扱われます。特別なことがなければbinaryのままで問題ありません。

character_set_results

MySQLからクライアントへ結果セットを返すときの文字コードになります。

クライアントからがあれば、MySQLからもあるよな。

character_set_server

MySQLで扱うデフォルトの文字コードです。

特別なことが無い限りは、character_set_clientとcharacter_set_serverに同じ文字コードを設定しておくことをおすすめします。

この設定だけ用意しておけばいいんじゃないの？

なんでこんな色々設定する箇所があるわけ。

table:

character_set_client クライアントが送ってくるとサーバーが想定している文字コード

character_set_connection クエリを実行する文字コード

character_set_database 使っているデータベースの文字コード。変える必要はないしsetで変えてはいけない。

character_set_filesystem ファイル名の文字コード。デフォルトでいい

character_set_results クライアントに結果を返すときの文字コード

character_set_server 新規にデータベースを作るときの文字コード。設定しておくと楽。いろいろ継承されるが上書きできる。

character_set_system テーブル名とかを保存する文字コード。デフォルトでいい

MySQLでは、クライアント文字コード、サーバー文字コードを別々に設定します。

文字コード指定単位 役割

クライアント文字コード クライアントがデータの送受信に使用する文字コード

サーバー文字コード 文字データを内部的に表現するための文字コード

クライアント側、サーバ側で異なる文字コードを指定している場合、MySQLの機能により文字コードの変換処理が行われます。

う〜〜〜ん、なんとなく言ってることはわかるんだが、図で理解したいな。

通常、Webアプリケーションでは「クライアント（ブラウザなど）の文字コード」「サーバー（PHPなど）の文字コード」「データベースの文字コード」と、独自に文字コードの設定を持っていますが、３つの文字コードがバラバラだとデータの受け渡しの際に文字コード変換処理を挟む必要があるため文字化けが発生しやすくなります。Webアプリケーションを開発する際には、３つの文字コードが同じとなるように気をつけましょう。

https://scrapbox.io/files/643f5c6d073399001c3d8232.png

まだこれだけじゃわからん

WebアプリケーションからMySQLデータベースへデータを格納するとき、どうしても文字コード変換処理が必要な場合は、下記を参考に「Webアプリケーション」「MySQLデータベース」の文字コード変換処理が適切に行われるようにしましょう。

table:

Webアプリケーションの文字コード MySQLデータベースの文字コード 「SET NAMES」構文 変換処理 文字化け

UTF-8 binary 指定なし 変換なし 文字化けなし

UTF-8 UTF-8 指定なし 変換なし 文字化けなし

EUC-JP UTF-8 指定なし 変換なし 文字化けなし

EUC-JP UTF-8 SET NAMES ujis 変換あり 文字化けなし

EUC-JP UTF-8 SET NAMES utf8 変換あり 文字化け発生

そして、クライアント側から送られてくるSQLの文字が想定と異なる場合は、文字化けが起きちゃうということだ。

ちなみに、ujisとEUC-JPの関係は..

SET NAMES ステートメント

このステートメントは、character_set_client、character_set_connection および character_set_results の 3 つのセッションシステム変数を特定の文字セットに設定します。 character_set_connection を charset_name に設定すると、collation_connection も charset_name のデフォルトの照合順序に設定されます。

一部の文字セットは、クライアントの文字セットとして使用できません。 SET NAMES でこれらを使用しようとすると、エラーが発生します。 許可されていないクライアント文字セットを参照してください。

インスタンス データベース テーブル 列 の4種類のレベルで文字コードを指定できます。

列の文字コードが指定されていなければ、テーブルの文字コード。

テーブルの文字コードが指定されていなければ、データベースの文字コード。

データベースの文字コードもしてされていなければ、インスタンスの文字コードが割り当てられます。

そのレベルで指定されてなかったら、上位レベルの文字コードを参照しに行くのね

各レベルの設定方法

table:

優先順位 レベル 設定方法

1 列 CREATE / ALTER TABLE文で指定

2 テーブル CREATE / ALTER TABLE文で指定

3 データベース CREATE / ALTER DATABASE文で指定

4 インスタンス 初期化パラメータdefault-character-set で指定

なるほどねぇ。

code: sample

〜

Server characterset: utf8mb4

Db characterset: utf8mb4

Client characterset: utf8mb4

Conn. characterset: utf8mb4

〜

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8mb3 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

MySQL8系では、文字コードがデフォルトでutf8mb4に設定されていることを知った。

MySQL5系では、デフォルトの文字コードがlatin1だったので、日本語を扱う場合は以下のように、my.cnfの設定で文字コードをUTF8に設定しなければならなかった。

code:sapmle

character-set-server=utf8

default-character-set=utf8

default-character-set=utf8

default-character-set=utf8

データベースの文字コードは INFORMATION_SCHEMA データベースの SCHEMATA テーブルから確認することができます。

code: sql

SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME

FROM INFORMATION_SCHEMA.SCHEMATA;

テーブルの文字コードと照合順序は INFORMATION_SCHEMA データベースの TABLES テーブルから確認することができます。

code: sql

SELECT TABLE_NAME,TABLE_COLLATION

FROM INFORMATION_SCHEMA.TABLES;

文字コードと利用できる照合順序の組み合わせは INFORMATION_SCHEMA データベースの COLLATION_CHARACTER_SET_APPLICABILITY テーブルから確認ができます。

利用できる照合順序が MySQL のバージョンによって違うので、照合順序を変更する前にどの照合順序が使えるかを調べると良いです。

code: sql

SELECT COLLATION_NAME,CHARACTER_SET_NAME

FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY;

カラムの文字コードと照合順序を確認する

code:sql

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'tbl_name'

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl' AND table_schema = 'hogedb' AND column_name LIKE 'v1';

これはつまり、UnicodeのコードポイントがU+FFFFを超える文字は、utf8では保存できないってことです。

絵文字などを保存したい場合はutf8mb4を使わないといけません。

UTF-8は「世界中の文字を1つの文字コードで表そうぜ！」な文字集合であるUnicodeを表すための文字符号化方式の1つなわけですが、本来UTF-8は、1～4バイトで文字を表すルールです。

なので、MySQLのutf8はそもそもUTF-8とは呼べないわけです

だって1～3バイトまでしか対応してないんだから。偽物ですよ偽物。

今後MySQLを使ったアプリを作っていく場合は、utf8ではなくてutf8mb4を選択した方が良いと思われます･･。

はい

UTF-8と言ったら普通は、1~4バイトのはずなんです。

ただ、MySQLではutf8と言ったら1~3バイトになってる。

utf8mb4が1~4バイト。

この違いはMySQLのみに見られる。

なぜそんなことをしたんだMySQL。。

データベースの文字コードは、配下に作成するテーブルの文字コードのデフォルトとして利用される。

データベースの文字コードを変換しても、既存テーブルの文字コードは変換されないので注意！！！

テーブルの文字コードは、データベースの文字コードが適切に設定されてるなら設定する必要なし。

はい

既存テーブルの文字コード変換しても、その配下のカラムは変更されないので注意！！！

変更したい場合は「ALTER TABLE tbl_name CONVERT TO CHARSET utf8mb4」を実施されたし！！！

クライアントツールにも文字コードを設定する必要あり。

ふむ、なるほど。いずれにせよ、mysqlに接続する場合は、クライアントが必要よな。SDKとか。ライブラリとかもクライアントだ。

え、でも、Mysql8.0だとどうなるんだ？

utf8とutf8mb4が混在することで起きること

utf8で扱えない文字は全て「？」になる。

ただ、MySQLのデフォルト設定だとエラーにならない。

sql_mode=STRICT_ALL_TABLES

文字コードの種類が異なれば，同じ文字を表す文字コードが異なる。

サーバーは、クライアントによって送信されたステートメントを character_set_client から character_set_connection に変換します。 例外: _utf8mb4 や_latin2 などのイントロデューサを持つ文字列リテラルの場合、イントロデューサは文字セットを決定します。 セクション10.3.8「文字セットイントロデューサ」を参照してください。

clientの文字コードからconnectionの文字コードに変換するのかなるほど。ほんで？